﻿using Abet.Mcp.Modelo.Entidades;
using Abet.Mcp.Modelo.Servicos.Dal;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Abet.Mcp.Modelo.Servicos
{
	public static class RelatorioExcelServico
	{
		public static Stream ObterRelatorioUsuarios()
		{
			using (ExcelPackage package = new ExcelPackage())
			{
				ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Usuários");
				
				worksheet.Cells[1, 1].Value = "Id";
				worksheet.Cells[1, 2].Value = "Nome";

				var usuarios = new List<Usuario>();

				using (var db = new AbetDb())
				{
					usuarios = db.Usuarios.ToList();
				}

				int row = 2;

				foreach (var usuario in usuarios)
				{
					worksheet.Cells[string.Format("A{0}", row)].Value = usuario.Id;
					worksheet.Cells[string.Format("B{0}", row)].Value = usuario.Nome;	
					row++;
				}

				using (var range = worksheet.Cells[1, 1, 1, 2])
				{
					range.Style.Font.Bold = true;
					range.Style.Fill.PatternType = ExcelFillStyle.Solid;
					range.Style.Fill.BackgroundColor.SetColor(Color.Black);
					range.Style.Font.Color.SetColor(Color.White);
					range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
				}

				worksheet.Cells["A1:B" + (row - 1).ToString()].AutoFilter = true;

				worksheet.Cells.AutoFitColumns(10);
				
				package.Workbook.Properties.Title = "Abet - Relatório de Usuários";
				package.Workbook.Properties.Author = "CWI Software";
				package.Workbook.Properties.Comments = "Relatório de todos os usuários cadastrados no sistema Abet.Mcp.";

			
				package.Workbook.Properties.Company = "CWI Software";
			
				Stream stream = new MemoryStream(package.GetAsByteArray());

				return stream;
			}
		}
	}
}
